6 ERP Integration
Introduction
Enterprise Resource Planning or ERP is a software that controlls various processes that are essential to running a business, including inventory and order management, accounting, human resources, customer relationship management (CRM), and other. One of the main benefits of ERP systems is single database so when change is made in one part of the system all other elements can see this change. Among the most important parts of ERP and any ecommerce solution are products, brand, categories of products, orders and similar elements so it makse a lot of sense that changes made in ERP shoud be refleced in F-Webshop.
Every synchronization with ERP systems starts with ERPIntegrationPlugin. ERPIntegrationPlugin contains list of Symfony commands. When a command is started F-Webshop dispatches custom event for retrieving data. ERP specific plugin (example FLeaderPlugin) listens for this event and when it is dispatched it retrieves data from ERP and returns it to original command. Command then creates pagination and cache files used in synchronization. In the process of prepearing data for sync another event is dispatched, this is mappings event. Every ERP system has its own data structure and nameing scheme so we use this event to map F-Webshop fields to fields received from ERP. ERPIntegrationPlugin then formats data and persists it in database.
Currently it is possible to synchronize following elements:
- Brands
- Categories
- Products
- Prices
- Quantities
- Users
- Images
- Attributes
- Attribute values
- Product attribute values
- Price lists
- Quantity rebate
- Actions rebate
- Plugins
Starting commands
Every command used in synchronization is stored within ERPIntegrationPlugin and they can be called individually but best practice is to use doSync.sh script to call them. Almost all commands store additional temporary files and doSync.sh manages these files automatically. doSync.sh receives multiple parameters that determine its beheviour. Parameters are: categories, brands, products, products_delete, prices, pricelists, quantities, images, attributes, attribute_values, product_attribute_values, sync_users, sync_quantites_rebate, sync_actions_rebate, plugins and additional_options. Depending on the parameters specific commands will be executed. If no parameters are specified all commands will be started. For example we can start synchronization using:
src/Plugins/ErpIntegrationPlugin/Command/doSync.sh
to sync synchronize everything or
src/Plugins/ErpIntegrationPlugin/Command/doSync.sh brands
to synchronize only brands.
As an example we can take a look at how brand synchronization is executed. First max_pages and page parameters are set. Since it is highly recommended to use pagination data these two parameters enable this. Page represents current page and max_page is the last page of synchronization. Next empire-cli:erp:sync-brands command is started with page parameter. This command then fetches data from ERP and prepares pagination data. Command the writes pagination data to brandPagination.sh and doSync.sh reads this data and sets max_pages parameter. If max_pages is greater than page parameter command will be executed again. Additional commands can be executed to process all of synchronized data after synchronization of all elements. Example of souch command is empire-cli:generate-translations-for-entities command that generates translations after brands are synchronized.
...
if ${syncBrands}; then
max_pages=1
page=1
while (($page <= $max_pages)); do
php ${console_path} empire-cli:erp:sync-brands ${page}
if [ ${page} == 1 ] && [ -f ${0%/*}/TmpFiles/brandPagination.sh ]; then
source ${0%/*}/TmpFiles/brandPagination.sh 2> /dev/null
fi
((page++))
done
php ${console_path} empire-cli:generate-translations-for-entities -b --env=prod --no-debug
fi
...
Synchronization command
Next we will take a look at example of synchronization command. First command prepares page parameter for pagination.
$page = $this->input->getArgument('page') ?? null;
After this command dispatches custom ErpIntegrationEvents::GET_BRANDS_EVENT event
$data = $this->dispatchEventWithResult(ErpIntegrationEvents::GET_BRANDS_EVENT, array('page' => $page));
Returned data contains rows and page keys. Rows key is data form ERP and page key is number of pages to be synchronized. Data about number of pages is than stored in pagination file. After this command fetches data already stored in F-Webshop:
$results = $this->get('alligator.brand_manager')->getAllBrandsIdAndCode();
$brandCodes = array_column($results, 'code');
This is done so system knows if brand is new or it is existing brand that should be updated. Next command fetches mappings data (this will be explained in detail later) from specific ERP plugin:
$mappings = $this->getMappings('brand');
$codeKey = $this->getMappingsValueForKey('code', $mappings);
$brandNameKey = $this->getMappingsValueForKey('brandName', $mappings);
This data maps field from ERP to field from F-Webshop. With ERP data and mappings data custom sql can be formated and executed:
$this->bnQuery .= 'WHEN code="'.$code.'" THEN "'.$brandName.'" ';
$this->sQuery .= 'WHEN code="'.$code.'" THEN '.$slug.' ';
$this->pnQuery .= 'WHEN code="'.$code.'" THEN '.$parentBrandName.' ';
...
$this->update();
$this->insert();
When it all comes together command looks something like this:
try {
$page = $this->input->getArgument('page') ?? null;
/** get data from erp */
$data = $this->dispatchEventWithResult(ErpIntegrationEvents::GET_BRANDS_EVENT, array('page' => $page));
if (!$data['rows']) {
$this->writeSuccess('No brands to sync! Sync successful!');
return;
}
/** if it is first page write max_pages parameter to shell script */
self::setPage($page, $data['page'], ERPTmpFiles::BRAND_LIST_PAGINATION_PATH);
/** get all brand erp codes - determines if update or insert should be done */
$results = $this->get('alligator.brand_manager')->getAllBrandsIdAndCode();
$brandCodes = array_column($results, 'code');
$em = $this->getContainer()->get('doctrine')->getManager();
$this->connection = $em->getConnection();
/** get mappings and keys */
$mappings = $this->getMappings('brand');
$codeKey = $this->getMappingsValueForKey('code', $mappings);
$brandNameKey = $this->getMappingsValueForKey('brandName', $mappings);
/** create query for every item */
foreach ($data['rows'] as $row) {
$code = $row[$codeKey];
$nameEvent = $this->dispatchEventWithResult('erp.integration.custom.brand.name.logic', array('row' => $row, 'existingBrands' => $results, 'newBrands' => $data['rows'], 'brandNameKey' => $brandNameKey));
$brandName = $nameEvent ?: $row[$brandNameKey];
$slug = $this->connection->quote(ErpFunctions::urlize($brandName), \PDO::PARAM_STR);
$parentBrandName = $this->getValueFromErpData('parentBrandName', $row, $mappings);
$parentBrandName = $parentBrandName ? '"'.$parentBrandName.'"' : 'NULL';
if (!\in_array($code, $brandCodes, false)) {
/** there is no brand */
$this->insertQuery .= '("'.$brandName.'", "REGULAR", "'.$slug.'", "'.$code.'", 0, '.$parentBrandName.'),';
} else {
/** update existing brands */
$this->bnQuery .= 'WHEN code="'.$code.'" THEN "'.$brandName.'" ';
$this->sQuery .= 'WHEN code="'.$code.'" THEN '.$slug.' ';
$this->pnQuery .= 'WHEN code="'.$code.'" THEN '.$parentBrandName.' ';
$this->erpCodes .= '"'.$code.'",';
}
}
$this->update();
$this->insert();
$this->writeSuccess(sizeof($data['rows']).' brands updated. Page: '.$page.' of '.$data['page']);
} catch (\Exception $e) {
$this->writeError($e->getFile().' ('.$e->getLine().'): '.$e->getMessage());
}
Getting data from ERP
As we saw first thing all commands do is retrieve data from ERP. This is done using standard Symfony event system. For every entity that is being synchronized custom event is dispatched (ex. ErpIntegrationEvents::GET_BRANDS_EVENT). List of all events is stored within ErpIntegrationEvents class.
/**
* Class ErpIntegrationEvents - Defines all event used to get data and execute custom events
*/
final class ErpIntegrationEvents {
/**
* Get products event
* @var string
*/
public const GET_PRODUCTS_EVENT = 'erp.integration.get.products';
...
Each ERP system has it own specific plugin. These plugins are dependent on ERPIntegration plugin and are used to implement specific communication to ERP. One of examples of this plugin is FLeader plugin. FLeader has a number of event listeners that are responsible for providing data from ERP. When listener is activated it sends API request to ERP server. This data is processed and returned to original command. ERP specific plugins can work in many different ways, for example data can be retrieved from API request or using direct connection to ERP database. Lets take a look at an example.
FLeader has custom FleaderGetDataListener listener.
services:
fleader.get.data.listener:
class: Plugins\FLeaderPlugin\Business\Event\FleaderGetDataListener
tags:
- { name: kernel.event_listener, event: erp.integration.get.categories, method: getCategoriesFromFleader }
- { name: kernel.event_listener, event: erp.integration.get.products, method: getProductsFromFleader }
- { name: kernel.event_listener, event: erp.integration.get.products.deleted, method: getDeletedProductsFromFleader }
- { name: kernel.event_listener, event: erp.integration.get.brands, method: getBrandsFromFleader }
...
As we can see FleaderGetDataListener listens for multiple events. All of these events are used to provide data to synchronization commands. So when erp.integration.get.brands event is dispatched getBrandsFromFleader function will be executed:
/**
* Get brands * @param EventWithResult $event
* @throws \Exception
*/
public function getBrandsFromFleader(EventWithResult $event)
{
$params = $event->getParam();
$api = new ApiBrands($params['page'] ?? 1, 100);
$data = json_decode($api::fetch(), true)['data'];
if ($data == NULL) {
throw new \Exception('Error while getting brand data from FLeader');
}
$event->setResult(array(
'rows' => $data['rows'],
'page' => $data['last_page']
));
}
This function create instance of ApiBrands class that internally creates request to api route for ERP. This data is decoded and formatted in appropriate response. As we can see in this example, ERP specific plugins should use pagination while working with data. This reduces load and speeds up the process.
Preparing F-Webshop data
Next thing all commands do is prepare data already stored in F-Webshop. For example when brand synchronization is started F-Webshop retrieves code and id of all brand already stored in database. This is done so the system knows if it should do update or insert new data.
/** get all brand erp codes - determines if update or insert should be done */
$results = $this->get('alligator.brand_manager')->getAllBrandsIdAndCode();
$brandCodes = array_column($results, 'code');
Since synchronization system uses pagination this will be executed for every page of data. I order to speed up and reduce load cache files are used. ErpCacheUtil is responsible for retrieving and cacheing this data.
/**
* Class ErpCacheUtil - Helper class designed to provide cached data from database
*/
class ErpCacheUtil
{
/** @var ProductManager */
private $productManager;
...
/** @var array */
private $attributeValueErpCodes = [];
...
/**
* Get all erp codes and ids of products
* Use file as cache, if there is no file create one
* @param string $filename
* @return array
* @throws \Exception
*/
public function setAllProductsIdAndCode($filename = ERPTmpFiles::PRODUCT_ID_AND_ERP_CODES_FILE_PATH)
{
try {
if (!file_exists($filename)) {
$dbData = $this->productManager->getAllProductsErpCodes();
/** create string from every row and write it to file */
$this->createArrayCacheFile($dbData, 'productIdAndErpCodes', $filename, function ($row) {
return '"'.$row['erpCode'].'" => '.$row['id'].',';
});
}
include $filename;
return $this->productIdAndErpCodes;
} catch (\Exception $e) {
throw new \Exception(sprintf('Product sync error! Error while creating id and erp codes cache file: %s', $e->getMessage()));
}
}
...
As we can se setAllProductsIdAndCode function reads data from database and stores it in specified file. So next time information abouth products is needed it will be read from file instead of querying database again.
Pagination
Since there is a posibiliti of very large amounts of data beeng transfered betwean ERP and F-Webshop and within F-Webshop itself it is best practice to use pagination. ERP Data should be alweys separated in multiple requests. All commands used in synchronization expect that ERP returns number of pages to by synced. This data is than used to controll synchronization. Titles of all files used to store pagination and cached data are located in ERPTmpFiles class, and those files are located in ERPTmpFiles directory within ERPIntegrationPlugin.
/**
* Class ERPTmpFiles. Class containing all file names that are used in synchronization
* These files include files for caching data and pagination among other files
*/
class ERPTmpFiles {
/**
* Path to directory of files * @var string
*/
public const TMP_FILES_DIRECTORY = __DIR__.'/../../Command/TmpFiles';
/**
* Store how many pages we need to sync * @var string */
public const PRODUCT_PAGINATION_FILE_PATH = self::TMP_FILES_DIRECTORY.'/productPagination.sh';
...
If these files are not managed properly some parts of synchronization can misbehave. So in order for synchronization to work properly before start of synchronization these files should be removed. This process of cleanup is managed by doSync.sh script.
Getting mappings
Data in ERP is specific to every ERP system so mappings information is used to connect fields from F-Webshop to fields in ERP. This data is stored in ERP specific plugins and is received using events. All events for mapping data are stored in ErpIntegrationEvents class. As example we can take a look at product mappings data.
/**
* Return mapping information for product
* @param EventWithResult $event
*/
public function getProductMappings(EventWithResult $event)
{
$data = [
'id' => [
'property' => 'id',
'ignoreUpdate' => true
],
'productName' => 'name',
'erpCode' => [
'property' => 'id',
'ignoreUpdate' => true
],
'updatedAt' => [
'default' => new \DateTime(),
],
'tags' => [
'property' => 'tags',
'function' => function ($param) { return explode(',', $param); },
'default' => []
],
'unit_of_measure' => [
'property' => 'unit',
'ignore' => true
],
...
];
$event->setResult($data);
}
Simplest form of mappings if just defining that productName filed in F-Webshop is connected to name filed in ERP data:
'productName' => 'name',
Product name needs to be updated every time sinchronization is executed, but this is not the case for all fields. For example id of product should not be changed when we are updating data. When we do not want to update a filed we can set ignoreUpdate flage, like this:
'id' => [
'property' => 'id',
'ignoreUpdate' => true
],
Other fields may not alweays retarn value from ERP but we can set them default values by using default key. Example is updateadAt filed that shoud be alweays set tu current data:
'updatedAt' => [
'default' => new \DateTime(),
],
If case we need to adjust data from ERP we can define closure functions. For example FLeader returns product tags as comma separated string but F-Webshop uses simple arrays to define tags. In this case we can define function kay and define function that converts input string to array:
tags' => [
'property' => 'tags',
'function' => function ($param) { return explode(',', $param); },
'default' => []
],
Custom events
Some of the commands also dispatch additional events for data manipulation. For example each implementation can have its own logic for product flags. So when product entity is created there is special event that allows implementation plugin to further alter data before it is saved in database. Examples of these events are:
erp.integration.user.update.custom.logic
erp.integration.images.custom.logic
erp.custom.product.quantity.actions
events.
Since multiple implementations can have same ERP system we need to create implementation specific plugin. This plugin should contain data specific for implementation, souche as API addres, databease connections or frequecies for synchorization.
Synchronization fields
Brands
List of brand used to group products
| FIELD | TYPE | DESCRIPTION |
|---|---|---|
| id | int(11) | Primary key |
| code | varchar(255) | Unique code of brand. If ERP does not return value use ID from ERP |
| brand_name | varchar(255) | Name of brand |
| position | varchar(255) | Position of brand in sorted list |
| is_visible | tinyint(1) | Position of brand in sorted list |
| seo_title | varchar(255) | Brand seo title |
| seo_description | longtext | Brand seo description |
| seo_keywords | longtext | Brand seo keywords |
Categories
List of product categories
| FIELD | TYPE | DESCRIPTION |
|---|---|---|
| id | int(11) | Primary key |
| name | int(11) | Category name |
| description | longtext | Category description |
| display | tinyint(1) | Enable disable category visibility |
| hide_from_menu | tinyint(1) | Show/hide category from menu |
| root_id | int(11) | Root category of the category tree |
| parent_id | int(11) | Category parent node |
| code | varchar(255) | Category code |
| lft | int(11) | Category left property for tree structure |
| lvl | int(11) | Category level property for tree structure |
| rgt | int(11) | Category right property for tree structure |
| slug | varchar(254) | Category slug |
| meta_title | longtext | Category meta title |
| meta_description | longtext | Category meta description |
| meta_keywords | longtext | Category meta keywords |
Products
List of products
| FIELD | TYPE | DESCRIPTION |
|---|---|---|
| id | int(11) | Primary key |
| brand_id | int(11) | Product brand |
| erp_code | varchar(255) | Code to connect product with ERP representation of entity |
| product_name | varchar(255) | Name of product |
| description | longtext | Description of product |
| description_html | longtext | Description of product as HTML |
| slug | varchar(255) | Product slug |
| permalink | varchar(255) | Product permalink, needs to be unique on database level |
| sku | varchar(255) | Sku of product |
| tags | longtext | Product tags, specific keywords(DC2Type:simple_array) |
| tax_value | double | Tax value for product |
| vat_value | double | Vat value for product |
| arrival_date | datetime | Date when product will be available |
| is_new | tinyint(1) | Product is new |
| on_sale | tinyint(1) | Product is on sale |
| on_total_sale | tinyint(1) | Product is on total sale |
| on_action | tinyint(1) | Product is on action |
| damaged_good | tinyint(1) | Product is damaged |
| keywords | longtext | Product keywords array |
| additional_keywords | longtext | Product additional keywords for search |
| product_search_options | longtext | Contains all product specific search terms |
| seo_title | varchar(255) | SEO title for product |
| seo_description | longtext | SEO description for product |
| seo_keywords | longtext | SEO keywords for product |
| unit_of_measure_id | int(11) | Unit of measure |
| package_id | int(11) | Type of packing |
| unit_of_measure_weight_id | int(11) | Unit of measure for weight |
| unit_of_measure_qty_per_package_id | int(11) | Unit of measure for quantity per package |
| view_count | int(11) | Number of times the product is viewed |
| sales | int(11) | Number of times the product is sold |
| width | double | Product width |
| height | double | Product height |
| depth | double | Product depth |
| weight | double | Product weight with weight of its packaging |
| net_weight | double | Product weight without weight of its packaging |
| qty_per_package | double | Number of products per package |
Prices
Synchronization of prices will insert new row in table and update current price on product
| FIELD | TYPE | DESCRIPTION |
|---|---|---|
| product | int(11) | ERP code of product |
| value | int(11) | Price for product |
Quantities
Synchronization of quantities will update SimpleProduct table. Apart from product quantities we can define values for product flags using this table
| FIELD | TYPE | DESCRIPTION |
|---|---|---|
| base_product_id | int(11) | Product |
| numberInStock | int(11) | Available quantity, total quantity decreased by reserved quantity |
| min_quantity | int(11) | Minimal quantity that can be added to cart |
| total_quantity | int(11) | Total quantity in stock, including reserved quantity |
| reserved_quantity | int(11) | Reserved quantity |
| min_quantity_light_value | int(11) | Minimal quantity light value. Defines value that separates red and yellow flags |
| max_quantity_light_value | int(11) | Maximal quantity light value. Defines value that separates yellow and green flags |
Users
List of users
| FIELD | TYPE | DESCRIPTION |
|---|---|---|
| id | int(11) | Primary key |
| address_id | int(11) | Address of user |
| username | varchar(255) | Username of user |
| varchar(255) | Email address of user | |
| enabled | tinyint(1) | Is user enabled |
| locked | tinyint(1) | Indicates if user account is locked |
| first_name | varchar(255) | User first name |
| last_name | varchar(255) | Last name of user |
| user_code | varchar(255) | User erp code |
| user_secondary_code | varchar(255) | User code |
| phone_numbers | longtext | Json array of phone numbers |
| gdpr_confirmed | tinyint(1) | Indicates if user has confirmed gdpr cookies |
| title | varchar(128) | Title of user |
| default_payment_type | varchar(255) | Payment which will be preselected on checkout |
| default_shipping_provider_id | int(11) | Shipping provider which will be preselected on checkout |
Address of user
Each user must have at least one address. When you are synchronizing users make sure you add address to every user
| FIELD | TYPE | DESCRIPTION |
|---|---|---|
| id | int(11) | Primary key |
| user_id | int(11) | User |
| active | tinyint(1) | Is address still active |
| phone_number | longtext | Phone number of address owner(DC2Type:json_array) |
| country | varchar(255) | Address country |
| township | varchar(255) | Address township |
| street | varchar(255) | Address street |
| street_number | varchar(255) | Address street number |
| apartment_number | varchar(255) | Address apartment number |
| floor_number | varchar(255) | Address floor number |
| city | varchar(255) | Address city |
| postcode | varchar(255) | Address postcode |
| erp_codeIndex | varchar(255) | Unique identifier for address. Used for storing address's erp code |
Images
Product images. Each image can have its code so no duplicates occur
| FIELD | TYPE | DESCRIPTION |
|---|---|---|
| id | int(11) | Primary key |
| product_id | int(11) | Product of image |
| name | varchar(255) | File name |
| code | varchar(255) | Unique identifier for image |
| priority | int(11) | Position of image. Used for sort images in products |
| not_resizable | tinyint(1) | Property that enables or disables image resizing on save |
| watermark_image | tinyint(1) | Use watermark over image or not |
| position | varchar(255) | Image position |
Attributes
List of all attributes. Color, size, gender are all examples of attributes
| FIELD | TYPE | DESCRIPTION |
|---|---|---|
| id | int(11) | Primary key |
| name | varchar(255) | Name of attribute |
| code | int(11) | Unique identifier for attribute |
| display_to_user | tinyint(1) | Should display on frontend |
| priority | int(11) | Position of attribute in attribute set |
| type | varchar(255) | Attribute type (CHECK, RADIO, SELECT) |
| attribute_type | varchar(255) | Attribute type (information type, price type) |
Attribute values
Specific attribute values. For example red, green, blue are all part of color attribute
| FIELD | TYPE | DESCRIPTION |
|---|---|---|
| id | int(11) | Primary key |
| attribute_id | int(11) | Attribute |
| code | int(11) | Unique identifier for attribute |
| priority | int(11) | Position in attribute |
| value | varchar(255) | Value name |
| color | varchar(255) | Color of button to display |
| display_text | tinyint(1) | Should display value name |
Product attribute values
Group of attributes in relation to products create Product attribute values. Product attribute value can have its own specific data (ex. quantity and sku)
| FIELD | TYPE | DESCRIPTION |
|---|---|---|
| id | int(11) | Primary key |
| product_id | int(11) | Product |
| code | int(11) | Code to connect product attribute values with ERP representation of entity |
| sku | varchar(255) | Sku of product attribute values |
| erp_code | varchar(255) | Code to connect product attribute values with ERP representation of entity |
| tax_value | int(11) | Tax value for product attribute values |
| quantity | int(11) | Available quantity, total quantity decreased by reserved quantity |
| total_quantity | int(11) | Total quantity in stock, including reserved quantity |
| reserved_quantity | int(11) | Reserved quantity |
| min_quantity | int(11) | Minimal quantity that can be added to cart |
| quantity_step | int(11) | Product attribute values quantity to add to cart |
| default_pav | tinyint(1) | Default product attribute value |
| display_to_user | tinyint(1) | Show product attribute values fo user |
| keywords | longtext | Product attribute values keywords |
| additional_keywords | longtext | Product attribute values additional keywords |
Price list
List of available price lists
| FIELD | TYPE | DESCRIPTION |
|---|---|---|
| id | int(11) | Primary key |
| name | varchar(255) | Name of pricelist |
| currency | varchar(255) | Currency string of pricelist |
| is_default | tinyint(1) | Is pricelist default |
| erp_code | varchar(255) | Erp code of pricelist |
| discount | double | Base discount |
| discounts | longtext | Array of quantity dependent discounts |
Price list item
Relation between product and price list
| FIELD | TYPE | DESCRIPTION |
|---|---|---|
| id | int(11) | Primary key |
| pricelist_id | int(11) | Pricelist |
| product_id | int(11) | Product |
| price | double | Price for item |
| action_price | double | Price for action |
Product group
Group of products with specific discounts
| FIELD | TYPE | DESCRIPTION |
|---|---|---|
| id | int(11) | Primary key |
| name | varchar(255) | Name of group |
| is_active | tinyint(1) | Is group active |
| date_from | datetime | Date from which the group is available |
| date_to | datetime | Date to which the group is available |
| keywords | varchar(255) | Products can be filtered by keywords |
| products | longtext | Final array of all ids of products in group |
| custom_products | longtext | (DC2Type:array) |
| base_discount | int(11) | Discount for quantity 1 |
| discounts | longtext | Array of discounts |
| priority | int(11) | Priority of group |
| base_fixed_discount | int(11) | Discount for quantity 1 |
| fixed_discounts_array | longtext | Array of fixed discounts |
Product group discount
Group of products with specific discounts for specific users and price lists
| FIELD | TYPE | DESCRIPTION |
|---|---|---|
| id | int(11) | Primary key |
| product_group_id | int(11) | Product group |
| limit_to_price_list_id | int(11) | Pricelist |
| base_discount | int(11) | Discount for quantity 1 |
| discounts | longtext | Array of discounts |
| base_fixed_discount | int(11) | Discount for quantity 1 |
| fixed_discounts_array | longtext | Discount for quantity 1 |